<?php
/*
Description:
	Contains functions, that create and execute mysql queries.

Function List:

1	insert_into_member($username,$password,$email,$type_id)
2	insert_into_game($title,$release_date,$category_id)
3	insert_into_comment($game_id,$member_id,$comment_text)
4	insert_rate($game_id,$member_id,$rate)

5	get_member_type_id($member_type)
6	get_game_category_id($category)
7	get_member_id($user)
8	get_game_id($title)
9	get_avg_rate($game_id)
10	check_user_exist($username)
11	check_game_exist($title)

12	show_games($order_by,$limitStart,$limitLength)
13	show_games_where($field,$field_value,$order_by,$limitStart,$limitLength)

14	get_comments($game_id,$limitStart,$limitLength)

15	login($username,$password)
16	logout()

*/

// include("DB_Access.php");


//1------------------------------------------------------------------------------------------------1
//Returns FALSE on failure to Insert data
function insert_into_member($username,$password,$email,$type_id)
{
	//Alaxa ton kwdika
	include("DB_Access.php");
	$query="INSERT INTO `member`(`username`, `password`, `type_id`, `email`) VALUES ('$username','$password','$email','$type_id')";
	$result=exec_sql_query($query); //returns TRUE or FALSE
	return $result;
}
//2------------------------------------------------------------------------------------------------2
//Returns FALSE on failure to Insert data
function insert_into_game($title,$release_date,$category_id)
	{
		include("DB_Access.php");
		$query="insert into game ( title, release_date, category_id) values ('$title','$release_date','$category_id')";
		$result= exec_sql_query($query);//returns TRUE or FALSE
		return $result;
	}
//3------------------------------------------------------------------------------------------------3
//Returns FALSE on failure to Insert data
function insert_into_comment($game_id,$member_id,$comment_text)
 {
  $query="Insert into comments (game_id,member_id,comment_text) values ('$game_id','$member_id','$comment_text')";
  $result=exec_sql_query($query);
  return $result;
 }
 
//4------------------------------------------------------------------------------------------------4
//Returns FALSE on failure to Insert data 
function insert_rate($game_id,$member_id,$rate)
{
	include("DB_Access.php");		
	/*Vasilis den xriazete connect kai den einai to swsto meros gia validations. 
	connect(); 
	$game_id = mysql_real_escape_string($game_id);
	$member_id = mysql_real_escape_string($member_id);
	$rate = mysql_real_escape_string($rate);*/
	$query="INSERT INTO `rate`(`game_id`, `member_id`, `rate`) VALUES ('$game_id','$member_id','$rate')";
	$result= exec_sql_query($query);
	return $result;
	//disconnect(); Den xriazete
		
}
//5------------------------------------------------------------------------------------------------5
//Returns type_id or FALSE
function get_member_type_id($member_type)
{
	include("DB_Access.php");
	$query="Select type_id from member_type where member_type='$member_type'";
	$result=exec_sql_query($query);
	if(!$result) // An to result einai FALSE
		return FALSE;
	else
	{
		$row=mysql_fetch_assoc($result);
		return $row['type_id'];
	}
}
//6------------------------------------------------------------------------------------------------6
//Returns category_id or FALSE
function get_game_category_id($category)
{
	include("DB_Access.php");
	$query="select category_id from game_category where category='$category'";
	// Mporei na veltiwthei wste na epistrefei ligotera dedomena.
	$result= exec_sql_query($query);
	//if(mysql_num_rows($result)>0) //an yparxei esto kai mia egrafi (borei na veliwthei)
	//{  
	if(!$result) // An to result einai FALSE
		return FALSE;
	else
	{
		$row=mysql_fetch_assoc($result);
		return $row['category_id'];
	}
}
//7------------------------------------------------------------------------------------------------7
//Returns member_id or FALSE
function get_member_id($user)
  {
	include("DB_Access.php");
    $query="Select member_id from member where username='$user'";
    $result=exec_sql_query($query);
	if(!$result) // An to result einai FALSE
		return FALSE;
	else
	{
		$row = mysql_fetch_assoc($result); 
		return $row['member_id']; 	
	}
  }
//8------------------------------------------------------------------------------------------------8
//Returns game_id or FALSE
function get_game_id($title)
  { 
	include("DB_Access.php");
    $query="Select game_id from game where title='$title'";
    $result=exec_sql_query($query);
	if(!$result) // An to result einai FALSE
		return FALSE;
	else
	{
		$row=mysql_fetch_assoc($result);
		return $row['game_id'];	
	}
  }
  
//9------------------------------------------------------------------------------------------------9
//Returns game_id or FALSE
function get_avg_rate($game_id)
{
  $query="Select avg(rate) from game inner join rate on game.game_id=rate.game_id where rate.game_id='$game_id' group by game_id";
  $avg_rate=exec_sql_query($query);
  return $avg_rate;

}

//10------------------------------------------------------------------------------------------------10
//Checks if the username allready exist in the database and returns TRUE or FALSE
function check_user_exists($username)
{
	include("DB_Access.php");
	$query = "SELECT `member_id` FROM `member` WHERE username='$username'"; //Alaxa to * se member_id
	$result= exec_sql_query($query);
	if(!$result) // An to result einai FALSE
		return FALSE;
	else 
		return TRUE;
	/*if(mysql_num_rows($result)>0) //an yparxei esto kai mia egrafi
	{   								//Vasilis afoson einai mia mono egrafei borei na bei kai mysql_num_rows($result)==1;
		return true;
	}
	else
		return false;*/
}
//11------------------------------------------------------------------------------------------------11
//Checks if the game allready exist in the database and returns true or false
function check_game_exist($title)
{
	include("DB_Access.php");
	$query="select game_id from game where title='$title'"; //alaxa to * se game id
	$result= exec_sql_query($query);
	if(!$result) // An to result einai FALSE
		return FALSE;
	else 
		return TRUE;
	/*if(mysql_num_rows($result)==0)
	{
		return false;
	}
	else
	{
		return true;
	}*/
	
}
//12------------------------------------------------------------------------------------------------12
//Returns array of games by order or FALSE
function show_games($order_by,$limitStart,$limitLength)
{
	include("DB_Access.php");
	$query="select * from game order_by '$order_by' LIMIT',$limitStart','$limitLength'";
	$result=exec_sql_query($query);
	return $result;
	/*if(!$result) // An to result einai FALSE
		return FALSE;
	else 
		return $result;*/  // to kanei idi h exec_mysql_query()
}
//13------------------------------------------------------------------------------------------------13
//Returns array of games by order or FALSE
function show_games_where($field,$field_value,$order_by,$limitStart,$limitLength)
{
	include("DB_Access.php");
	$query="select * from game where '$field'='$field_value' 
	order_by '$order_by' LIMIT'$limitStart','$limitLength'";
	$result=exec_sql_query($query);
	return $result;
}
//14------------------------------------------------------------------------------------------------14
//Returns array of comments ordered by date (latest to oldest) or FALSE
function get_comments($game_id,$limitStart,$limitLength)
{
	include("DB_Access.php");
	$query="SELECT * FROM  `comments` WHERE game_id='$game_id' 
	order by comment_id desc LIMIT '$limitStart','$limitLength'"; //date DESC == ID DESC
	$result=exec_sql_query($query);											
	return $result;
}
//15------------------------------------------------------------------------------------------------15
/*Returns TRUE or FALSE for Authentication*/
function login($username,$password)
{
	include("DB_Access.php");
	$query="SELECT `member_id` FROM `member` WHERE username='$username' and password='$password'"; //to alaxa apo * se member_id
	$result=exec_sql_query($query);
	if(mysql_num_rows($result)>0) //an yparxei esto kai mia egrafi
	{   
		return true;
	}
	else
		return false;
}
?>